package br.com.sga.modelo.dao;

import br.com.sga.util.conexao.Pool;
import br.com.sga.erro.ConexaoException;
import br.com.sga.erro.DaoException;
import br.com.sga.modelo.entidade.Sysfuncao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author kenzo
 */
public class SysfuncaoDaoImpl implements SysfuncaoDao{
    private Pool pool;
    private String sqlInserir = "INSERT INTO sysfuncoes (nome,regra,descricao,titulo,status) VALUES(?,?,?,?,?)";
    private String sqlAtualizar = "UPDATE sysfuncoes SET nome=?,regra=?,descricao=?,titulo=?,status=? WHERE (id_sysfuncao=?)";
    private String sqlExcluir = "DELETE FROM sysfuncoes WHERE (id_sysfuncao=?)";
    private String sqlConsultar = "SELECT nome,regra,descricao,titulo,status FROM sysfuncoes WHERE (id_sysfuncao=?)";
    private String sqlListarAtivos = "SELECT nome,regra,descricao,titulo,status FROM sysfuncoes WHERE (stratus=1)";
    private String sqlListarTodos = "SELECT nome,regra,descricao,titulo,status FROM sysfuncoes";
    
    public SysfuncaoDaoImpl(Pool pool){
        this.pool = pool;
    }

    @Override
    public void salvar(Sysfuncao bean) throws ConexaoException, DaoException{
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlInserir);
            
            pst.setString(1, bean.getNome());
            pst.setString(2, bean.getRegra());
            pst.setString(3, bean.getDescricao());
            pst.setString(4, bean.getTitulo());
            pst.setInt(5, bean.getStatus());
            pst.executeUpdate();

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
        
    }

    @Override
    public void atualizar(Sysfuncao bean) throws ConexaoException, DaoException {
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlAtualizar);
            
            pst.setString(1, bean.getNome());
            pst.setString(2, bean.getRegra());
            pst.setString(3, bean.getDescricao());
            pst.setString(4, bean.getTitulo());
            pst.setInt(5, bean.getStatus());
            pst.setInt(6, bean.getId());
            pst.executeUpdate();

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
    }

    @Override
    public void excluir(Integer id) throws ConexaoException, DaoException {
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlExcluir);
            
            pst.setInt(1, id);
            pst.executeUpdate();

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
    }

    @Override
    public Sysfuncao getBean(Integer id) throws ConexaoException, DaoException {
        Sysfuncao bean = null;
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlConsultar);
            
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if( rs.next() ){
                bean = new Sysfuncao();
                bean.setDescricao( rs.getString("descricao") );
                bean.setNome( rs.getString("nome") );
                bean.setRegra( rs.getString("regra") );
                bean.setTitulo( rs.getString("titulo") );
                bean.setStatus( rs.getInt("status") );
                bean.setId( id );
            }

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
        return bean;
    }

    @Override
    public List<Sysfuncao> getBeans() throws ConexaoException, DaoException {
        ArrayList<Sysfuncao> lista = new ArrayList<>();
        Sysfuncao bean = null;
        Connection c = pool.getConnection();
        Statement st;
        try{
            st = c.createStatement();
            
            ResultSet rs = st.executeQuery(sqlListarTodos);
            while( rs.next() ){
                bean = new Sysfuncao();
                bean.setDescricao( rs.getString("descricao") );
                bean.setNome( rs.getString("nome") );
                bean.setRegra( rs.getString("regra") );
                bean.setTitulo( rs.getString("titulo") );
                bean.setStatus( rs.getInt("status") );
                bean.setId( rs.getInt("id_sysfuncao") );
            }

            st.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
        return lista;
    }
    
    public List<Sysfuncao> getBeansAtivos() throws ConexaoException, DaoException {
        ArrayList<Sysfuncao> lista = new ArrayList<>();
        Sysfuncao bean = null;
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlListarAtivos);
            
            ResultSet rs = pst.executeQuery();
            while( rs.next() ){
                bean = new Sysfuncao();
                bean.setDescricao( rs.getString("descricao") );
                bean.setNome( rs.getString("nome") );
                bean.setRegra( rs.getString("regra") );
                bean.setTitulo( rs.getString("titulo") );
                bean.setStatus( rs.getInt("status") );
                bean.setId( rs.getInt("id_sysfuncao") );
            }

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
        return lista;
    }
}
